﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web;

namespace Cashflow.DAL.SqlClient
{
	public class SqlReExProvider : ReExProvider
	{
		public override ReExDetails GetReExByID(long id)
		{
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				//ReEx_GetReExByID
				SqlCommand cmd = new SqlCommand("ReEx_GetReExByID", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = id;
				cn.Open();
				IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
				if (reader.Read())
					return GetReExFromReader(reader);
				else
					return null;
			}
		}

		public override List<ReExDetails> GetReExsFromTo(DateTime from, DateTime to)
		{
			//ReEx_GetReExsFromTo
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_GetReExsFromTo", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@From", SqlDbType.DateTime).Value = from;
				cmd.Parameters.Add("@To", SqlDbType.DateTime).Value = to;
				cn.Open();
				return GetReExCollectionFromReader(ExecuteReader(cmd));
			}
		}

		public override List<ReExDetails> GetReExsByYear(int year)
		{
			//ReEx_GetReExsByYear
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_GetReExsByYear", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
				cn.Open();
				return GetReExCollectionFromReader(ExecuteReader(cmd));
			}
		}

		public override List<ReExDetails> GetReExsByMonth(int year, int month)
		{
			//ReEx_GetReExsByMonth
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_GetReExsByMonth", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
				cmd.Parameters.Add("@Month", SqlDbType.Int).Value = month;
				cn.Open();
				return GetReExCollectionFromReader(ExecuteReader(cmd));
			}
		}

		public override List<ReExDetails> GetReExsByDay(int year, int month, int day)
		{
			//ReEx_GetReExsByDay
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_GetReExsByDay", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
				cmd.Parameters.Add("@Month", SqlDbType.Int).Value = month;
				cmd.Parameters.Add("@Day", SqlDbType.Int).Value = day;
				cn.Open();
				return GetReExCollectionFromReader(ExecuteReader(cmd));
			}
		}

		public override int InsertReEx(ReExDetails reex)
		{
			//ReEx_InsertReEx
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_InsertReEx", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = reex.Date;
				cmd.Parameters.Add("@Amount", SqlDbType.Money).Value = reex.Amount;
				cmd.Parameters.Add("@Note", SqlDbType.NVarChar).Value = reex.Note;
				cmd.Parameters.Add("@ID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
				cn.Open();
				int ret = ExecuteNonQuery(cmd);
				return (int)cmd.Parameters["@ID"].Value;
			}
		}

		public override bool UpdateReEx(ReExDetails reex)
		{
			//ReEx_UpdateReEx
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_UpdateReEx", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = reex.ID;
				cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = reex.Date;
				cmd.Parameters.Add("@Amount", SqlDbType.Money).Value = reex.Amount;
				cmd.Parameters.Add("@Note", SqlDbType.NVarChar).Value = reex.Note;
				cn.Open();
				int ret = ExecuteNonQuery(cmd);
				return (ret == 1);
			}
		}

		public override bool DeleteReEx(long id)
		{
			//ReEx_DeleteReEx
			using (SqlConnection cn = new SqlConnection(this.ConnectionString))
			{
				SqlCommand cmd = new SqlCommand("ReEx_DeleteReEx", cn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = id;
				cn.Open();
				int ret = ExecuteNonQuery(cmd);
				return (ret == 1);
			}
		}
	}
}